﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using SqlOrm.Xml;
using System.Diagnostics;
using System.Text.RegularExpressions;
using SqlOrm.Models;
using System.Configuration;
using System.Data.Common;
using System.Data.SqlServerCe;
using System.Data;
using ForeignKeyConstraint = SqlOrm.Models.ForeignKeyConstraint;

namespace SqlOrm
{
    public class SqlDDLParser
    {
        DbConnection _conn;
        bool _keepAlive = false;
        PrimaryKeys _keys;
        List<ForeignKeyConstraint> _foriegnKeys;
        List<TableIndex> _indexes;
        bool _ce;

        public SqlDDLParser(string connectionString, bool ce)
        {
            _ce = ce;
            if (ce)
                _conn = new SqlCeConnection(connectionString);
            else
                _conn = new SqlConnection(connectionString);

            GetPrimaryKeys();
            GetTableIndexes();
            GetForiegnKeyConstraints();
        }

        public void ExecuteSql(string query)
        {
            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = _conn.CreateCommand())
                command.ExecuteNonQuery();
            if (!_keepAlive)
                _conn.Close();
        }
        public DataTable ExecuteSqlQuery(string query)
        {
            DataSet ds;
            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = _conn.CreateCommand())
            {
                command.CommandText = query;
                using (IDataReader rdr = command.ExecuteReader())
                {
                    ds = new DataSet { EnforceConstraints = false };
                    ds.Load(rdr, LoadOption.PreserveChanges, "");
                }
            }
            if (!_keepAlive)
                _conn.Close();
            return ds.Tables[0];
        }

        private void GetTableIndexes()
        {
            _indexes = new List<TableIndex>();
            string qry;
            if (_ce)
            {
                qry = @"select table_schema, table_name, index_name,primary_key,[unique],[clustered]
,type,nulls,ordinal_position,column_name,
cast((case when [collation] = 1 then 1 else 0 end) as bit) as descending
from information_schema.indexes";
            }
            else
            {
                qry = @"select
	s.name 'table_schema',
	o.name 'table_name',
    i.name 'index_name', 
    i.is_primary_key 'primary_key',
    i.is_unique 'unique',
    cast((case when i.type_desc = 'CLUSTERED' then 1 else 0 end) as bit) 'clustered',
    i.[type] 'type',
    ic.key_ordinal as 'ordinal_position',
    co.[name] as 'column_name',
    ic.is_descending_key 'descending'
from sys.indexes i 
inner join sys.objects o on i.object_id = o.object_id
inner join sys.index_columns ic on ic.object_id = i.object_id  and ic.index_id = i.index_id
inner join sys.columns co on co.object_id = i.object_id 
inner join sys.schemas s on s.schema_id = o.schema_id
    and co.column_id = ic.column_id
where --i.name = 'u_Person' and 
o.[type] = 'U'
order by o.[name], i.[name], ic.is_included_column, ic.key_ordinal";
            }
            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = _conn.CreateCommand())
            {
                command.Connection = _conn;
                command.CommandText = qry;
                using (var rdr = command.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        var i = _indexes.FirstOrDefault(x => x.IndexName == (string)rdr["index_name"] 
                            && x.Schema == (rdr["Table_schema"] == DBNull.Value ? null : (string)rdr["Table_schema"]) 
                            && x.Table == (string)rdr["table_name"]);
                        if (i == null)
                        {
                            i = new TableIndex();
                            i.Schema = rdr["Table_schema"] == DBNull.Value ? null : (string)rdr["Table_schema"];
                            i.Table = (string)rdr["table_name"];
                            i.Clustered = (bool)rdr["clustered"];
                            i.PrimaryKey = (bool)rdr["primary_key"];
                            i.IndexName = (string)rdr["index_name"];
                            i.Unique = (bool)rdr["unique"];
                            i.Columns = new List<ColumnIndex>();
                            _indexes.Add(i);
                        }
                        i.Columns.Add(new ColumnIndex
                        {
                            ColumnName = (string)rdr["column_name"],
                            Descending = (bool)rdr["descending"]
                        });
                    }
                }
            }
        }

        private void GetPrimaryKeys()
        {
            //u.Table_schema, u.table_name, u.column_name, u.constraint_name
            _keys = new PrimaryKeys();
//            string query = @"SELECT u.Table_schema, u.table_name, u.column_name, u.constraint_name
//FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
//JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u ON tc.CONSTRAINT_NAME = u.Constraint_name
//WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'";
            string query = @"selecT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.Table_schema, 
kcu.COLUMN_NAME,kcu.ORDINAL_POSITION, kcu.CONSTRAINT_NAME
from information_schema.TABLE_CONSTRAINTS tc
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
where CONSTRAINT_TYPE = 'PRIMARY KEY'";
            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = _conn.CreateCommand())
            {
                command.Connection = _conn;
                command.CommandText = query;
                using (var rdr = command.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        var k = new PrimaryKeyItem();
                        k.TableSchema = rdr["Table_schema"] == DBNull.Value ? null : (string)rdr["Table_schema"];
                        k.TableName = (string)rdr["table_name"];
                        k.ColumnName = (string)rdr["column_name"];
                        k.ConstraintName = (string)rdr["constraint_name"];
                        _keys.Add(k);
                    }
                }
            }
            if (!_keepAlive)
                _conn.Close();
        }

        public void GetForiegnKeyConstraints()
        {
            const string qry = @"SELECT 
     KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
    ,KCU1.TABLE_NAME AS FK_TABLE_NAME
    ,KCU1.CONSTRAINT_SCHEMA as FK_SCHEMA_NAME
    ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
    ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
    ,RC.UNIQUE_CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
    ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME
    ,KCU2.CONSTRAINT_SCHEMA as REFERENCED_SCHEMA_NAME
    ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME 
    ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 

LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1 
    ON --KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND 
     (KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA or (KCU1.CONSTRAINT_SCHEMA is null and RC.CONSTRAINT_SCHEMA is null)) AND
     KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 

LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 
    ON --KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  AND 
     (KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA or (KCU2.CONSTRAINT_SCHEMA is null and RC.UNIQUE_CONSTRAINT_SCHEMA is null)) AND
     KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME AND 
     KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION";
            var result = new List<ForeignKeyConstraint>();
            var dt = ExecuteSqlQuery(qry);
            var count = 0;
            foreach (DataRow dr in dt.Rows)
            {
                bool primaryKeyReference=true;

                var fk = result.FirstOrDefault(x => x.FKConstraintName == (string)dr["FK_CONSTRAINT_NAME"]);
                if (fk == null)
                {
                    result.Add(fk = new ForeignKeyConstraint { FKConstraintName = (string)dr["FK_CONSTRAINT_NAME"] });
                    fk.FKTableName = (string)dr["FK_TABLE_NAME"];
                    fk.FKSchemaName = dr["FK_SCHEMA_NAME"] == DBNull.Value ? null : (string)dr["FK_SCHEMA_NAME"];
                    fk.RefConstraintName = (string)dr["REFERENCED_CONSTRAINT_NAME"];
                    primaryKeyReference = dr["REFERENCED_TABLE_NAME"] != DBNull.Value;
                    if (!primaryKeyReference)
                    {
                        var ind = _indexes.Where(x => x.IndexName == fk.RefConstraintName);
                        if (ind.Count() == 0 || ind.Count() > 1)
                            //throw new Exception("Unable to determine reference for [{0}]".F(fk.RefConstraintName));
                            Console.WriteLine("Unable to determine reference for [{0}]".F(fk.RefConstraintName));
                        else
                        {
                            //Debugger.Break();
                            fk.RefTableName = ind.First().Table;
                            fk.RefSchemaName = ind.First().Schema;
                            fk.Columns = (from c in ind.First().Columns
                                         let ord = Array.IndexOf(ind.First().Columns.ToArray(), c)
                                          select new ForeignKeyConstraintColumn { FKColumnName = (string)dr["FK_COLUMN_NAME"], FKOrdinal = ord, RefOrdinal = ord, RefColumnName = c.ColumnName }).ToList();
                        }
                    }
                    else
                    {
                        fk.RefTableName = (string)dr["REFERENCED_TABLE_NAME"];
                        fk.RefSchemaName = dr["REFERENCED_SCHEMA_NAME"] == DBNull.Value ? null : (string)dr["REFERENCED_SCHEMA_NAME"];
                    }
                }
                if (primaryKeyReference)
                {
                    var cols = new ForeignKeyConstraintColumn();
                    cols.FKColumnName = (string)dr["FK_COLUMN_NAME"];
                    cols.FKOrdinal = (int)dr["FK_ORDINAL_POSITION"];
                    cols.RefColumnName = (string)dr["REFERENCED_COLUMN_NAME"];
                    cols.RefOrdinal = (int)dr["REFERENCED_ORDINAL_POSITION"];
                    fk.Columns.Add(cols);
                }
                count++;
            }
            _foriegnKeys = result;
        }

        private void GetProcsParamData(Procedure proc)
        {
            var result = new List<Field>();
            string query = string.Format(@"select parameter_name, data_type, character_maximum_length
from Information_Schema.parameters
where specific_schema = @schema and specific_name = @name");
            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = _conn.CreateCommand() )
            {
                command.CommandText = query;
                command.Connection = _conn;
                command.AddWithValue("@name", proc.name);
                command.AddWithValue("@schema", proc.schema);
                using (var rdr = command.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        var fd = new Field();
                        fd.name = (string)rdr["parameter_name"];
                        fd.name = fd.name.Replace("@", "");
                        fd.len = rdr["character_maximum_length"] != DBNull.Value ? Convert.ToInt32(rdr["character_maximum_length"]) : -1;
                        FieldDataType fdt;
                        if (!Enum.TryParse((string)rdr["data_type"], true, out fdt)) // DataTypeConversion.GetType((string)rdr["data_type"]);
                            if (!Enum.TryParse("@" + (string)rdr["data_type"], true, out fdt))
                                throw new Exception((string)rdr["data_type"] + " does not convert to FieldDataType");
                        fd.datatype = fdt;
                        //ApplyStandardFieldRules(fd);
                        result.Add(fd);
                    }
                }
            }
            if (!_keepAlive)
                _conn.Close();
            proc.Input = result.ToArray();
        }


        private List<Field> GetFieldData(TableDefinition t)
        {
            var result = new List<Field>();
//            string query = string.Format(@"select s.name'schema',t.name'table', c.name'column',ty.name'type',c.is_nullable,is_identity, is_computed,c.max_length
//from sys.columns c
//inner join sys.tables t on c.object_id = t.object_id
//inner join sys.schemas s on s.schema_id = t.schema_id
//inner join sys.types ty on c.system_type_id = ty.system_type_id and c.system_type_id = ty.user_type_id
//--inner join sys.types tys on ty.user_type_id = tys.user_type_id
//where s.name = @schema and t.name = @table 
//order by s.name,t.name", t.name, t.schema);
            string query = string.Format(@"select Column_Name, Data_Type, Is_Nullable,character_Maximum_length, autoinc_increment, column_flags
from information_schema.columns
where table_name = '{0}'
order by table_name, ordinal_position", t.name);
            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = _conn.CreateCommand())
            {
                command.CommandText = query;
                command.Connection = _conn;
                command.AddWithValue("@table", t.name);
                command.AddWithValue("@schema", t.schema);
                using (var rdr = command.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        var fd = new Field();
                        fd.name = (string)rdr["Column_Name"];
                        var flags = (int)rdr["column_flags"];
                        fd.rowguidcol = (flags == 378 || flags == 282); // sql compact flags stack overflow
                        fd.nulls = (string)rdr["is_nullable"] == "YES";
                        fd.len = rdr["character_Maximum_length"] != DBNull.Value ? Convert.ToInt32(rdr["character_Maximum_length"]) : -1;
                        fd.identity = rdr["autoinc_increment"] != DBNull.Value;
                        FieldDataType fdt;
                        if (!Enum.TryParse((string)rdr["Data_Type"], true, out fdt)) // DataTypeConversion.GetType((string)rdr["data_type"]);
                            if (!Enum.TryParse("@" + (string)rdr["Data_Type"], true, out fdt))
                                throw new Exception((string)rdr["Data_Type"] + " does not convert to FieldDataType");
                        fd.datatype = fdt;
                        fd.datatypeSpecified = true;
                        result.Add(fd);
                    }
                }
            }
            var fks = _foriegnKeys.Where(x => x.FKSchemaName == t.schema && x.FKTableName == t.name).ToList();
            foreach (var fk in fks)
            {
                if (fk.Columns.Count != 1)
                    throw new Exception("Composite primary keys not yet implemented");
                var xmlfk = new ForeignKey { fkname = fk.FKConstraintName, schema = fk.RefSchemaName, table = fk.RefTableName, column = fk.Columns[0].RefColumnName };
                result.First(x => x.name == fk.Columns[0].FKColumnName).FK = xmlfk;
            }

            foreach (var k in _keys.Where(x => x.TableSchema == t.schema && x.TableName == t.name))
                result.First(x => x.name == k.ColumnName).key = true;
            if (!_keepAlive)
                _conn.Close();
            //t.Field = result.ToArray();
            return result;
        }

        private List<TableDefinition> GetTableData()
        {
            var result = new List<TableDefinition>();
            string query = @"select Table_Schema, Table_Name from information_schema.tables";

            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = _conn.CreateCommand())
            {
                command.Connection = _conn;
                command.CommandText = query;
                using (var rdr = command.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        var td = new TableDefinition();
                        td.name = (string)rdr["Table_Name"];
                        td.schema = (rdr["Table_Schema"] == DBNull.Value) ? null : (string)rdr["Table_Schema"];

                        var indeces = _indexes.Where(x => !x.PrimaryKey && x.Table == td.name && x.Schema == td.schema);
                        if (indeces.Count() > 0)
                        {
                            var xmlindeces = new List<Index>();
                            foreach (var index in indeces)
                            {
                                var xmlindex = new Index { unique = index.Unique, name = index.IndexName, type = (index.Clustered) ? IndexType.clustered : IndexType.nonclustered };
                                var xmlindexFields = new List<IndexField>();
                                foreach (var indexCol in index.Columns)
                                    xmlindexFields.Add(new IndexField { column = indexCol.ColumnName, sort = (indexCol.Descending) ? Xml.SortOrder.desc : Xml.SortOrder.asc });
                                xmlindex.IndexField = xmlindexFields.ToArray();
                                xmlindeces.Add(xmlindex);
                            }
                            td.Indexes = new Indexes { Index = xmlindeces.ToArray() };
                        }
                        result.Add(td);
                    }
                }
            }
            if (!_keepAlive)
                _conn.Close();
            return result;
        }

        private List<Procedure> GetProcsData()
        {
            var result = new List<Procedure>();

            string q = @"select routine_catalog, routine_schema, routine_name
from Information_Schema.routines r
where r.Routine_Type = 'PROCEDURE'
order by routine_schema, routine_name";
            if (_conn.State != System.Data.ConnectionState.Open)
                _conn.Open();
            using (var command = _conn.CreateCommand())
            {
                command.CommandText = q;
                command.Connection = _conn;
                using (var rdr = command.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        var td = new Procedure { Origin = Origin.SQL };
                        td.name = (string)rdr["routine_name"];
                        td.schema = (string)rdr["routine_schema"];
                        result.Add(td);
                    }
                }
            }
            if (!_keepAlive)
                _conn.Close();

            return result;
        }

        internal void CreateProcs(DataDictionary ddl)
        {
            Console.WriteLine("Creating procs");
            if (ddl.Definition.Schema == null)
                ddl.Definition.Schema = new DDLSchema();
            if (ddl.Definition.Procedures == null)
                ddl.Definition.Procedures = new Procedure[0];

            var xprocs = ddl.Definition.Procedures.ToList();
            var dprocs = (ddl.Settings.Database.ver == DatabaseVersion.Sql2008R2) ? GetProcsData() : new List<Procedure>();

            var matches = (from x in xprocs.Where(x => x.pattern != null)
                           select new { Pattern = x, Procs = dprocs.Where(d => Regex.IsMatch(d.FullName, x.pattern)) }).ToArray();

            foreach (var match in matches)
            {
                Console.WriteLine("pattern: " + match.Pattern.pattern + " (" + match.Procs.Count() + ")");
                foreach (var proc in match.Procs)
                {
                    if (!match.Pattern.exclude)
                    {
                        if (proc.Input == null)
                            GetProcsParamData(proc);
                        MergeProcs(match.Pattern, proc);
                        if (!xprocs.Contains(proc))
                            xprocs.Add(proc);
                    }
                }
            }

            Array.ForEach(xprocs.Where(x => x.pattern != null).ToArray(), x => xprocs.Remove(x));
            ddl.Definition.Procedures = xprocs.ToArray();
        }

        public void CreateTables(DataDictionary ddl)
        {
            Console.WriteLine("Creating tables");
            if (ddl.Definition == null)
                ddl.Definition = new DDLSchemaDefinition();
            if (ddl.Definition.Schema == null)
                ddl.Definition.Schema = new DDLSchema();
            if (ddl.Definition.Schema.Table == null)
                ddl.Definition.Schema.Table = new TableDefinition[0];


            var xtables = ddl.Definition.Schema.Table.ToList();
            var dtables = GetTableData();

            var matches = (from x in xtables.Where(x => x.pattern != null)
                     select new { Pattern = x, Tables = dtables.Where(d => Regex.IsMatch(d.FullName, x.pattern)) }).ToArray();

            foreach (var match in matches)
            {
                Console.WriteLine("pattern: " + match.Pattern.pattern + " (" + match.Tables.Count() + ")");
                foreach (var tbl in match.Tables)
                {
                    if (!match.Pattern.exclude)
                    {
                        if (tbl.Field == null)
                        {
                            var fields = GetFieldData(tbl);
                            if (match.Pattern.Field != null)
                            {
                                var fms = (from pf in match.Pattern.Field.Where(x => !string.IsNullOrEmpty(x.pattern))
                                           select new { Pattern = pf, Fields = fields.Where(f => Regex.IsMatch(f.name, pf.pattern)) }).ToList();
                                foreach (var fm in fms)
                                    foreach (var f in fm.Fields)
                                        MergeField(fm.Pattern, f);
                            }
                            tbl.Field = fields.ToArray();
                        }
                        MergeTables(match.Pattern, tbl);
                        if (!xtables.Contains(tbl))
                            xtables.Add(tbl);
                    }
                }
            }

            Array.ForEach(xtables.Where(x => x.pattern != null).ToArray(), x => xtables.Remove(x));
            ddl.Definition.Schema.Table = xtables.ToArray();
        }

        public void MergeField(Field pattern, Field field)
        {
            if (pattern.datatypeSpecified)
            {
                field.datatypeSpecified = true;
                field.datatype = pattern.datatype;
            }
            if (!string.IsNullOrEmpty(pattern.@default))
                field.@default = pattern.@default;
            if (pattern.Enum != null)
                field.Enum = pattern.Enum;
            if (pattern.FK != null)
                field.FK = pattern.FK;
            if (pattern.specialSpecified)
            {
                field.specialSpecified = true;
                field.special = pattern.special;
            }
        }

        public void MergeTables(TableDefinition pattern, TableDefinition table)
        {
            if (pattern.activefield != null)
                table.activefield = pattern.activefield;
            if (pattern.allSpecified)
            {
                table.allSpecified = true;
                table.all = pattern.all;
            }
            if (pattern.getSpecified)
            {
                table.getSpecified = true;
                table.get = pattern.get;
            }
            if (pattern.datacontractSpecified)
            {
                table.datacontractSpecified = true;
                table.datacontract = pattern.datacontract;
            }
            if (pattern.deleteSpecified)
            {
                table.deleteSpecified = true;
                table.delete = pattern.delete;
            }
            if (pattern.excludeSpecified)
            {
                table.excludeSpecified = true;
                table.exclude = pattern.exclude;
            }
            //if (pattern.Field != null)
            //    table.Field = pattern.Field;
            if (pattern.search != null)
                table.search = pattern.search;
            if (pattern.Searches != null)
                table.Searches = pattern.Searches;
            if (pattern.upsertSpecified)
            {
                table.upsertSpecified = true;
                table.upsert = pattern.upsert;
            }
            if (pattern.updateSpecified)
            {
                table.updateSpecified = true;
                table.update = pattern.update;
            }
            if (pattern.insertSpecified)
            {
                table.insertSpecified = true;
                table.insert = pattern.insert;
            }
        }

        public void MergeProcs(Procedure pattern, Procedure table)
        {
            //if (pattern.activefield != null)
            //    table.activefield = pattern.activefield;
            //if (pattern.allSpecified)
            //    table.all = pattern.all;
            //if (pattern.datacontractSpecified)
            //    table.datacontract = pattern.datacontract;
            //if (pattern.deleteSpecified)
            //    table.delete = pattern.delete;
            if (pattern.excludeSpecified)
                table.exclude = pattern.exclude;
            if (pattern.Input != null)
                table.Input = pattern.Input;
            if (pattern.Output != null)
                table.Output = pattern.Output;
            //if (pattern.search != null)
            //    table.search = pattern.search;
            //if (pattern.upsertSpecified)
            //    table.upsert = pattern.upsert;
            //if (pattern.updateSpecified)
            //    table.update = pattern.update;
            //if (pattern.insertSpecified)
            //    table.insert = pattern.insert;
        }

    }
}
